﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace SSISLib
{
    /// <summary>
    /// CRM API class
    /// </summary>
    public class CRMAPI
    {
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="url"></param>
        /// <param name="orgName"></param>
        public CRMAPI(string url, string orgName, string user, string password, int timeOut, BlueIntegrator.DAL.DALBase dal)
        {
            m_url = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, url);
            m_orgName = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, orgName);
            m_user = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, user);
            m_password = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, BlueIntegrator.Helpers.Encryption.DecryptString(password, "CRMConfig"));
            m_timeOut = timeOut;
        }

        string m_url, m_orgName, m_user, m_password;
        int m_timeOut = 60;

        /// <summary>
        /// Service accessor
        /// </summary>
        /// <returns></returns>
        public SSISLib.CRMMetaService.MetadataService GetMetaService()
        {
            // Create an authentication token for your organization.
            SSISLib.CRMMetaService.CrmAuthenticationToken token = new SSISLib.CRMMetaService.CrmAuthenticationToken();
            // Replace 'AdventureWorksCycle' with your Microsoft Dynamics CRM organization name.
            token.OrganizationName = m_orgName;
            // Use Active Directory authentication.
            token.AuthenticationType = 0;

            string metaUrl = m_url;

            if (metaUrl.EndsWith("/"))
                metaUrl = metaUrl.Substring(0, metaUrl.Length - 1);

            metaUrl += "/MSCRMServices/2007/MetaDataService.asmx";

            // Set up the metadata service.
            SSISLib.CRMMetaService.MetadataService mdSevice = new SSISLib.CRMMetaService.MetadataService(metaUrl);

            if (m_user != null)
                mdSevice.Credentials = new System.Net.NetworkCredential(m_user, m_password);
            else
                mdSevice.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // Substitute the name of your Microsoft Dynamics CRM server.
            mdSevice.Url = metaUrl;

            // Set the token.
            mdSevice.CrmAuthenticationTokenValue = token;
            mdSevice.UnsafeAuthenticatedConnectionSharing = true;
            mdSevice.Timeout = m_timeOut * 1000;
            return mdSevice;
        }

        /// <summary>
        /// Service accessor
        /// </summary>
        /// <returns></returns>
        public SSISLib.CRMService.CrmService GetCRMService()
        {
            // Create an authentication token for your organization.
            SSISLib.CRMService.CrmAuthenticationToken token = new SSISLib.CRMService.CrmAuthenticationToken();
            // Replace 'AdventureWorksCycle' with your Microsoft Dynamics CRM organization name.
            token.OrganizationName = m_orgName;
            // Use Active Directory authentication.
            token.AuthenticationType = 0;

            string crmUrl = m_url;

            if (crmUrl.EndsWith("/"))
                crmUrl = crmUrl.Substring(0, crmUrl.Length - 1);

            crmUrl += "/MSCrmServices/2007/CrmService.asmx";

            // Set up the metadata service.
            SSISLib.CRMService.CrmService mdSevice = new SSISLib.CRMService.CrmService(crmUrl);

            if (m_user != null)
                mdSevice.Credentials = new System.Net.NetworkCredential(m_user, m_password);
            else
                mdSevice.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // Substitute the name of your Microsoft Dynamics CRM server.
            mdSevice.Url = crmUrl;

            // Set the token.
            mdSevice.CrmAuthenticationTokenValue = token;
            mdSevice.UnsafeAuthenticatedConnectionSharing = true;
            mdSevice.Timeout = m_timeOut * 1000;

            return mdSevice;
        }

        #region Name caches
        static System.Collections.Hashtable sm_NameToGUIDCache = System.Collections.Specialized.CollectionsUtil.CreateCaseInsensitiveHashtable();
        static System.Collections.Hashtable sm_GUIDToNameCache = System.Collections.Specialized.CollectionsUtil.CreateCaseInsensitiveHashtable();
        static string sm_cacheURL = "";
        #endregion

        /// <summary>
        /// Accessor for list of entities
        /// </summary>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<string, string>[] GetAllEntities()
        {
            SSISLib.CRMMetaService.MetadataService service = GetMetaService();

            SSISLib.CRMMetaService.RetrieveAllEntitiesRequest req = new SSISLib.CRMMetaService.RetrieveAllEntitiesRequest();
            SSISLib.CRMMetaService.RetrieveAllEntitiesResponse resp = service.Execute(req) as SSISLib.CRMMetaService.RetrieveAllEntitiesResponse;

            List<System.Collections.Generic.KeyValuePair<string, string>> result = new List<System.Collections.Generic.KeyValuePair<string, string>>();
            List<string> nameResult = new List<string>();
            System.Collections.Hashtable resultHash = new System.Collections.Hashtable();

            lock (typeof(CRMAPI))
            {
                if (sm_cacheURL != m_url)
                {
                    sm_NameToGUIDCache.Clear();
                    sm_GUIDToNameCache.Clear();
                    sm_cacheURL = m_url;
                }
            }

            foreach (SSISLib.CRMMetaService.EntityMetadata currentEntity in resp.CrmMetadata)
            {
                resultHash[currentEntity.SchemaName.ToString()] = (new System.Collections.Generic.KeyValuePair<string, string>(currentEntity.MetadataId.Value.ToString(), currentEntity.SchemaName.ToString()));

                lock (typeof(CRMAPI))
                {
                    sm_NameToGUIDCache[currentEntity.SchemaName.ToString()] = currentEntity.MetadataId.Value.ToString();
                    sm_NameToGUIDCache[currentEntity.MetadataId.Value.ToString()] = currentEntity.MetadataId.Value.ToString();
                    sm_GUIDToNameCache[currentEntity.SchemaName.ToString()] = currentEntity.SchemaName.ToString();
                    sm_GUIDToNameCache[currentEntity.MetadataId.Value.ToString()] = currentEntity.SchemaName.ToString();
                }

                nameResult.Add(currentEntity.SchemaName.ToString());
            }

            nameResult.Sort();

            foreach (string name in nameResult)
            {
                result.Add((System.Collections.Generic.KeyValuePair<string, string>)resultHash[name]);
            }

            return result.ToArray();
        }

        /// <summary>
        /// Accessor for attributes associated with an entity
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public Attribute[] GetAttributes(string entity)
        {
            SSISLib.CRMMetaService.MetadataService service = GetMetaService();

            SSISLib.CRMMetaService.RetrieveEntityRequest req = new SSISLib.CRMMetaService.RetrieveEntityRequest();
            req.MetadataId = new System.Guid(entity);
            req.EntityItems = SSISLib.CRMMetaService.EntityItems.All;

            SSISLib.CRMMetaService.RetrieveEntityResponse resp = service.Execute(req) as SSISLib.CRMMetaService.RetrieveEntityResponse;

            List<Attribute> result = new List<Attribute>();

            // iterate attributes
            foreach (SSISLib.CRMMetaService.AttributeMetadata ameta in resp.EntityMetadata.Attributes)
            {
                Attribute attr = new Attribute();
                attr.name = ameta.SchemaName.ToString();
                attr.crmType = ameta.AttributeType.Value;
                attr.required = ameta.RequiredLevel != null && !ameta.RequiredLevel.IsNull && (ameta.RequiredLevel.Value == CRMMetaService.AttributeRequiredLevel.Required || ameta.RequiredLevel.Value == CRMMetaService.AttributeRequiredLevel.SystemRequired);

                switch (ameta.AttributeType.Value)
                {
                    case SSISLib.CRMMetaService.AttributeType.Boolean:
                        attr.ssisType = "bit";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.DateTime:
                        attr.ssisType = "datetime";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.Decimal:
                        attr.ssisType = "numeric(18,0)";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.Float:
                        attr.ssisType = "float";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.Integer:
                        attr.ssisType = "int";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.PrimaryKey:
                        attr.ssisType = "guid";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.Money:
                        attr.ssisType = "money";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.String:
                        attr.ssisType = "nvarchar(4000)";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.UniqueIdentifier:
                        attr.ssisType = "guid";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.Picklist:
                    case SSISLib.CRMMetaService.AttributeType.State:
                    case SSISLib.CRMMetaService.AttributeType.Status:
                    case SSISLib.CRMMetaService.AttributeType.PartyList:
                        attr.ssisType = "nvarchar(1024)";
                        break;

                    case SSISLib.CRMMetaService.AttributeType.Lookup:
                    case SSISLib.CRMMetaService.AttributeType.Owner:
                    case SSISLib.CRMMetaService.AttributeType.Customer:
                        attr.ssisType = "guid";
                        break;
                }

                if (attr.ssisType != null)
                    result.Add(attr);
            }

            result.Sort();
            return result.ToArray();
        }

        /// <summary>
        /// Accessor for relationships associated with an Entity
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public Relationship[] GetRelationships(string entity)
        {
            SSISLib.CRMMetaService.MetadataService service = GetMetaService();

            SSISLib.CRMMetaService.RetrieveEntityRequest req = new SSISLib.CRMMetaService.RetrieveEntityRequest();
            req.MetadataId = new System.Guid(entity);
            req.EntityItems = SSISLib.CRMMetaService.EntityItems.All;

            SSISLib.CRMMetaService.RetrieveEntityResponse resp = service.Execute(req) as SSISLib.CRMMetaService.RetrieveEntityResponse;

            List<Relationship> result = new List<Relationship>();

            // iterate relationships
            foreach (SSISLib.CRMMetaService.OneToManyMetadata rmeta in resp.EntityMetadata.OneToManyRelationships)
            {
                Relationship rel = new Relationship();
                rel.relationshipId = rmeta.MetadataId.Value.ToString();

                rel.type = Relationship.RelationshipType.OneToMany;
                rel.currentEntity = GetEntityID(rmeta.ReferencedEntity);
                rel.currentAttribute = rmeta.ReferencedAttribute;
                rel.relatedEntity = GetEntityID(rmeta.ReferencingEntity);
                rel.relatedAttribute = rmeta.ReferencingAttribute;

                rel.name = "Link from [" + rmeta.ReferencingEntity + "]." + rmeta.ReferencingAttribute + " to " + rel.relatedAttribute + " (1:*)";

                result.Add(rel);
            }

            foreach (SSISLib.CRMMetaService.ManyToManyMetadata rmeta in resp.EntityMetadata.ManyToManyRelationships)
            {
                Relationship rel = new Relationship();
                rel.relationshipId = rmeta.MetadataId.Value.ToString();
                rel.type = Relationship.RelationshipType.ManyToMany;

                string entity1Id = GetEntityID(rmeta.Entity1LogicalName);
                string entity2Id = GetEntityID(rmeta.Entity2LogicalName);
                string intEntityId = GetEntityID(rmeta.IntersectEntityName);

                if (intEntityId == entity)
                {
                    if (entity2Id != entity && entity1Id != entity)
                    {
                        // add 2 relationships
                        Relationship rel1 = new Relationship();
                        rel1.relationshipId = rmeta.MetadataId.Value.ToString() + "_" + entity1Id;
                        rel1.type = Relationship.RelationshipType.ManyToMany;
                        rel1.relatedEntity = entity1Id;
                        rel1.relatedAttribute = rmeta.Entity1IntersectAttribute;
                        rel1.currentEntity = intEntityId;
                        rel1.currentAttribute = rmeta.Entity2IntersectAttribute;
                        rel1.name = rmeta.Entity1LogicalName;
                        result.Add(rel1);

                        Relationship rel2 = new Relationship();
                        rel2.relationshipId = rmeta.MetadataId.Value.ToString() + "_" + entity2Id;
                        rel2.type = Relationship.RelationshipType.ManyToMany;
                        rel2.relatedEntity = entity2Id;
                        rel2.relatedAttribute = rmeta.Entity2IntersectAttribute;
                        rel2.currentEntity = intEntityId;
                        rel2.currentAttribute = rmeta.Entity1IntersectAttribute;
                        rel2.name = rmeta.Entity2LogicalName;
                        result.Add(rel2);
                        continue;
                    }

                    
                    if (entity == entity2Id)
                    {
                        rel.relatedEntity = entity1Id;
                        rel.relatedAttribute = rmeta.Entity1IntersectAttribute;
                        rel.currentEntity = entity2Id;
                        rel.currentAttribute = rmeta.Entity2IntersectAttribute;
                    }
                    else
                    {
                        rel.relatedEntity = entity2Id;
                        rel.relatedAttribute = rmeta.Entity2IntersectAttribute;
                        rel.currentEntity = entity1Id;
                        rel.currentAttribute = rmeta.Entity1IntersectAttribute;
                    }
                }
                else
                {
                    if (entity == entity2Id)
                    {
                        rel.relatedEntity = GetEntityID(rmeta.IntersectEntityName);
                        rel.relatedAttribute = rmeta.Entity1IntersectAttribute;
                        rel.currentEntity = entity2Id;
                        rel.currentAttribute = rmeta.Entity2IntersectAttribute;
                    }
                    else
                    {
                        rel.relatedEntity = GetEntityID(rmeta.IntersectEntityName);
                        rel.relatedAttribute = rmeta.Entity2IntersectAttribute;
                        rel.currentEntity = entity1Id;
                        rel.currentAttribute = rmeta.Entity1IntersectAttribute;
                    }
                }
                
                rel.name = rmeta.IntersectEntityName + " (*:*)";

                result.Add(rel);
            }

            foreach (SSISLib.CRMMetaService.OneToManyMetadata rmeta in resp.EntityMetadata.ManyToOneRelationships)
            {
                Relationship rel = new Relationship();
                rel.relationshipId = rmeta.MetadataId.Value.ToString();
                rel.type = Relationship.RelationshipType.ManyToOne;
                rel.currentEntity = GetEntityID(rmeta.ReferencingEntity);
                rel.currentAttribute = rmeta.ReferencingAttribute;
                rel.relatedAttribute = rmeta.ReferencedAttribute;
                rel.relatedEntity = GetEntityID(rmeta.ReferencedEntity);

                rel.name = "Link to [" + rmeta.ReferencedEntity + "]." + rmeta.ReferencedAttribute + " from " + rel.relatedAttribute + " (*:1)";

                result.Add(rel);
            }

            result.Sort();
            return result.ToArray();
        }

        /// <summary>
        /// Static interface
        /// </summary>
        /// <param name="properties"><param>
        /// <param name="data"></param>
        public static System.Xml.XmlDocument GetData(System.Collections.Hashtable properties, System.Xml.XmlDocument data, BlueIntegrator.DAL.DALBase dal)
        {
            string url = properties["SPURL"] as string;
            string user = properties["SPUser"] as string;
            string fields = properties["SPRelevantColumns"] as string ?? "";
            string org = properties["SPOrg"] as string;

            string pwd = BlueIntegrator.Helpers.Encryption.DecryptString(properties["SPPwd"] as string ?? "", "CRMConfig");
            int timeOut = SafeGetInt(properties["SPTimeout"], 60);

            CRMAPI crm = new CRMAPI(url, org, user, pwd, timeOut, dal);

            List<string> scopeEntities = new List<string>();
            List<string> seenEntities = new List<string>();

            List<BindingDefn> bindings = new List<BindingDefn>();
            List<Relationship> relationships = new List<Relationship>();

            int nRelBindings = SafeGetInt(properties["NumRelBindings"]);
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bf = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            bf.AssemblyFormat = System.Runtime.Serialization.Formatters.FormatterAssemblyStyle.Simple;
            bf.TypeFormat = System.Runtime.Serialization.Formatters.FormatterTypeStyle.TypesWhenNeeded;

            for (int i = 0; i < nRelBindings; i++)
            {
                string bindingInfo = properties["RelBinding:" + i] as string;

                if (bindingInfo != null)
                {
                    System.IO.Compression.GZipStream unzipper = new System.IO.Compression.GZipStream(new System.IO.MemoryStream(System.Convert.FromBase64String(bindingInfo)), System.IO.Compression.CompressionMode.Decompress, true);
                    CRMAPI.Relationship r = (CRMAPI.Relationship)bf.Deserialize(unzipper);
                    relationships.Add(r);
                }
            }

            int nBindings = SafeGetInt(properties["NumTBindings"]);

            for (int i = 0; i < nBindings; i++)
            {
                string bindingInfo = properties["Binding:" + i] as string;

                if (bindingInfo != null)
                {
                    System.IO.Compression.GZipStream unzipper = new System.IO.Compression.GZipStream(new System.IO.MemoryStream(System.Convert.FromBase64String(bindingInfo)), System.IO.Compression.CompressionMode.Decompress, true);
                    CRMAPI.BindingDefn b = (CRMAPI.BindingDefn)bf.Deserialize(unzipper);
                    b.filterValue = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, b.filterValue);
                    bindings.Add(b);
                }
            }

            return crm.GetDataXml (bindings.ToArray(), relationships.ToArray());
        }

        /// <summary>
        /// Data accessor
        /// </summary>
        /// <param name="bindings"></param>
        /// <returns></returns>
        public System.Xml.XmlDocument GetDataXml(BindingDefn[] bindings, Relationship[] relationships)
        {
            System.Xml.XmlDocument result = new System.Xml.XmlDocument();
            result.LoadXml("<BlueIntegratorCRM/>");

            try
            {
                try
                {
                    System.Array.Sort(bindings);
                    EntityBinding binding = ConvertToEntityBinding(bindings, relationships);

                    // Get root entities...
                    SSISLib.CRMService.BusinessEntity[] rootEntities = GetRootEntities(binding);

                    foreach (SSISLib.CRMService.DynamicEntity e in rootEntities)
                    {
                        System.Xml.XmlElement el = result.CreateElement(GetEntityName(binding.entityName));
                        result.DocumentElement.AppendChild(el);
                        ProcessObject(e, binding, el);
                    }

                    return result;
                }
                catch (System.Web.Services.Protocols.SoapException exc)
                {
                    System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                    throw rethrownExc;
                }
            }
            catch (System.Exception exc0)
            {
                System.Text.StringBuilder sb = new StringBuilder();
                sb.Append("Bindings : ");
                int n = 0;

                foreach (BindingDefn bd in bindings)
                {
                    if (n > 0)
                    {
                        sb.Append(", ");
                    }

                    sb.Append(bd.attributeDisplayPath ?? "");
                    sb.Append(" ");
                    sb.Append(bd.mappingType.ToString());
                    sb.Append(" ");
                    sb.Append(bd.sourceMapping ?? "");
                    sb.Append(" (");
                    sb.Append(bd.filterValue ?? "");
                    sb.Append(")");
                }

                if (exc0 is System.Web.Services.Protocols.SoapException)
                    throw new System.Web.Services.Protocols.SoapException(exc0.Message + System.Environment.NewLine + sb.ToString(), (exc0 as System.Web.Services.Protocols.SoapException).Code);
                else
                    throw new System.Exception(exc0.Message + System.Environment.NewLine + sb.ToString());
            }
        }
    
        /// <summary>
        /// Data accessor
        /// </summary>
        /// <param name="bindings"></param>
        /// <returns></returns>
        public List<System.Collections.Generic.KeyValuePair<string, object>[]> GetData(BindingDefn[] bindings, Relationship[] relationships)
        {
            try
            {
                try
                {
                    List<System.Collections.Generic.KeyValuePair<string, object>[]> result = new List<KeyValuePair<string, object>[]>();
                    System.Array.Sort(bindings);
                    EntityBinding binding = ConvertToEntityBinding(bindings, relationships);

                    // Get root entities...
                    SSISLib.CRMService.BusinessEntity[] rootEntities = GetRootEntities(binding);

                    foreach (SSISLib.CRMService.DynamicEntity e in rootEntities)
                    {
                        List<List<System.Collections.Generic.KeyValuePair<string, object>>> rows = new List<List<KeyValuePair<string, object>>>();
                        rows.Add(new List<KeyValuePair<string, object>>());
                        ProcessObject(e, binding, rows);

                        foreach (List<System.Collections.Generic.KeyValuePair<string, object>> row in rows)
                            result.Add(row.ToArray());
                    }

                    return result;
                }
                catch (System.Web.Services.Protocols.SoapException exc)
                {
                    System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                    throw rethrownExc;
                }
            }
            catch (System.Exception exc0)
            {
                System.Text.StringBuilder sb = new StringBuilder();
                sb.Append("Bindings : ");
                int n = 0;

                foreach (BindingDefn bd in bindings)
                {
                    if (n > 0)
                    {
                        sb.Append(", ");
                    }

                    sb.Append(bd.attributeDisplayPath ?? "");
                    sb.Append(" ");
                    sb.Append(bd.mappingType.ToString());
                    sb.Append(" ");
                    sb.Append(bd.sourceMapping ?? "");
                    sb.Append(" (");
                    sb.Append(bd.filterValue ?? "");
                    sb.Append(")");
                }

                if (exc0 is System.Web.Services.Protocols.SoapException)
                    throw new System.Web.Services.Protocols.SoapException(exc0.Message + System.Environment.NewLine + sb.ToString(), (exc0 as System.Web.Services.Protocols.SoapException).Code);
                else
                    throw new System.Exception(exc0.Message + System.Environment.NewLine + sb.ToString());
            }
        }

        /// <summary>
        /// Root entity accessor
        /// </summary>
        /// <param name="binding"></param>
        /// <param name="bindings"></param>
        /// <returns></returns>
        SSISLib.CRMService.DynamicEntity[] GetRootEntities(EntityBinding binding)
        {
            // execute query for entities of the given type
            List<SSISLib.CRMService.DynamicEntity> result = new List<SSISLib.CRMService.DynamicEntity>();
            SSISLib.CRMService.CrmService serv = GetCRMService();

            SSISLib.CRMService.QueryExpression query = new SSISLib.CRMService.QueryExpression();
            query.EntityName = this.GetEntityName(binding.entityName).ToLower();
            query.ColumnSet = new SSISLib.CRMService.AllColumns();

            foreach (BindingAttribute bd in binding.attributes)
            {
                if ((bd.mappingType == BindingDefn.MappingType.Key || (int)bd.mappingType >= 10))
                {
                    // it's a condition...
                    if (query.Criteria == null)
                    {
                        query.Criteria = new SSISLib.CRMService.FilterExpression();
                        query.Criteria.FilterOperator = SSISLib.CRMService.LogicalOperator.And;
                    }

                    SSISLib.CRMService.ConditionExpression expr = new SSISLib.CRMService.ConditionExpression();
                    expr.AttributeName = bd.name.ToLower();
                    expr.Operator = MapCRMOperator(bd.mappingType);
                    object filterVal = ConvertToAppropriateType(query.EntityName, bd.filterValue, bd.crmType);

                    if (bd.mappingType == BindingDefn.MappingType.Condition_In && filterVal is string)
                    {
                        string[] subs = filterVal.ToString().Split(',');
                        System.Collections.ArrayList objectArray = new System.Collections.ArrayList();
                        objectArray.AddRange(subs);
                        expr.Values = objectArray.ToArray();
                    }
                    else
                    {
                        if (filterVal == System.DBNull.Value || filterVal == null)
                        {
                            if (expr.Operator == CRMService.ConditionOperator.Equal)
                            {
                                expr.Operator = CRMService.ConditionOperator.Null;
                            }
                            else if (expr.Operator == CRMService.ConditionOperator.NotEqual)
                            {
                                expr.Operator = CRMService.ConditionOperator.NotNull;
                            }
                            else
                            {
                                expr.Values = new object[] { filterVal };
                            }
                        }
                        else
                        {
                            if (bd.mappingType == BindingDefn.MappingType.Condition_Between && filterVal is string)
                            {
                                string[] subs = filterVal.ToString().Split(',');
                                System.Collections.ArrayList objectArray = new System.Collections.ArrayList();
                                objectArray.AddRange(subs);
                                expr.Values = objectArray.ToArray();
                            }
                            else if (expr.Operator != CRMService.ConditionOperator.Null && expr.Operator != CRMService.ConditionOperator.NotNull
                                && expr.Operator != CRMService.ConditionOperator.Last7Days
                                && expr.Operator != CRMService.ConditionOperator.LastMonth
                                && expr.Operator != CRMService.ConditionOperator.LastWeek
                                && expr.Operator != CRMService.ConditionOperator.ThisMonth
                                && expr.Operator != CRMService.ConditionOperator.ThisWeek
                                && expr.Operator != CRMService.ConditionOperator.ThisYear
                                && expr.Operator != CRMService.ConditionOperator.Next7Days
                                && expr.Operator != CRMService.ConditionOperator.NextMonth
                                && expr.Operator != CRMService.ConditionOperator.NextWeek
                                )
                            {
                                expr.Values = new object[] { filterVal };
                            }
                        }
                    }

                    List<SSISLib.CRMService.ConditionExpression> conditions = new List<SSISLib.CRMService.ConditionExpression>();

                    if (query.Criteria.Conditions != null)
                        conditions.AddRange(query.Criteria.Conditions);

                    conditions.Add(expr);
                    query.Criteria.Conditions = conditions.ToArray();
                }
            }

            SSISLib.CRMService.RetrieveMultipleRequest req = new SSISLib.CRMService.RetrieveMultipleRequest();
            req.Query = query;
            req.ReturnDynamicEntities = true;
            SSISLib.CRMService.RetrieveMultipleResponse resp = serv.Execute(req) as SSISLib.CRMService.RetrieveMultipleResponse;

            foreach (SSISLib.CRMService.DynamicEntity ent in resp.BusinessEntityCollection.BusinessEntities)
            {
                result.Add(ent);
            }

            return result.ToArray();
        }

        /// <summary>
        /// Root entity accessor
        /// </summary>
        /// <param name="binding"></param>
        /// <param name="bindings"></param>
        /// <returns></returns>
        SSISLib.CRMService.DynamicEntity[] GetRelatedEntities(SSISLib.CRMService.DynamicEntity entity, EntityRelationship rel)
        {
            // execute query for entities of the given type
            List<SSISLib.CRMService.DynamicEntity> result = new List<SSISLib.CRMService.DynamicEntity>();
            SSISLib.CRMService.CrmService serv = GetCRMService();

            // now get relationship
            SSISLib.CRMService.QueryExpression query = new SSISLib.CRMService.QueryExpression();
            query.EntityName = this.GetEntityName(rel.entityName).ToLower();
            query.ColumnSet = new SSISLib.CRMService.AllColumns();

            // add relationship critera
            query.Criteria = new SSISLib.CRMService.FilterExpression();
            query.Criteria.FilterOperator = SSISLib.CRMService.LogicalOperator.And;

            SSISLib.CRMService.ConditionExpression expr = new SSISLib.CRMService.ConditionExpression();
            expr.AttributeName = rel.relatedAttribute.ToLower();
            expr.Operator = SSISLib.CRMService.ConditionOperator.Equal;

            // get attribute value
            object currentVal = GetPropertyValue(entity, rel.currentAttribute);
            expr.Values = new object[] { currentVal };

            List<SSISLib.CRMService.ConditionExpression> conditions = new List<SSISLib.CRMService.ConditionExpression>();
            conditions.Add(expr);

            foreach (BindingAttribute bd in rel.attributes)
            {
                if ((bd.mappingType == BindingDefn.MappingType.Key || (int)bd.mappingType >= 10))
                {
                    expr = new SSISLib.CRMService.ConditionExpression();
                    expr.AttributeName = bd.name.ToLower();
                    expr.Operator = MapCRMOperator(bd.mappingType);

                    object filterVal = ConvertToAppropriateType(query.EntityName, bd.filterValue, bd.crmType);

                    if (bd.mappingType == BindingDefn.MappingType.Condition_In && filterVal is string)
                    {
                        string[] subs = filterVal.ToString().Split(',');
                        System.Collections.ArrayList objectArray = new System.Collections.ArrayList();
                        objectArray.AddRange(subs);
                        expr.Values = objectArray.ToArray();
                    }
                    else
                    {
                        if (filterVal == System.DBNull.Value || filterVal == null)
                        {
                            if (expr.Operator == CRMService.ConditionOperator.Equal)
                            {
                                expr.Operator = CRMService.ConditionOperator.Null;
                            }
                            else if (expr.Operator == CRMService.ConditionOperator.NotEqual)
                            {
                                expr.Operator = CRMService.ConditionOperator.NotNull;
                            }
                            else
                            {
                                expr.Values = new object[] { filterVal };
                            }
                        }
                        else
                        {
                            if (bd.mappingType == BindingDefn.MappingType.Condition_Between && filterVal is string)
                            {
                                string[] subs = filterVal.ToString().Split(',');
                                System.Collections.ArrayList objectArray = new System.Collections.ArrayList();
                                objectArray.AddRange(subs);
                                expr.Values = objectArray.ToArray();
                            }
                            else if (expr.Operator != CRMService.ConditionOperator.Null && expr.Operator != CRMService.ConditionOperator.NotNull
                                && expr.Operator != CRMService.ConditionOperator.Last7Days
                                && expr.Operator != CRMService.ConditionOperator.LastMonth
                                && expr.Operator != CRMService.ConditionOperator.LastWeek
                                && expr.Operator != CRMService.ConditionOperator.ThisMonth
                                && expr.Operator != CRMService.ConditionOperator.ThisWeek
                                && expr.Operator != CRMService.ConditionOperator.ThisYear
                                && expr.Operator != CRMService.ConditionOperator.Next7Days
                                && expr.Operator != CRMService.ConditionOperator.NextMonth
                                && expr.Operator != CRMService.ConditionOperator.NextWeek
                                )
                            {
                                expr.Values = new object[] { filterVal };
                            }
                        }
                    }

                    conditions.Add(expr);
                }
            }

            query.Criteria.Conditions = conditions.ToArray();

            SSISLib.CRMService.RetrieveMultipleRequest req = new SSISLib.CRMService.RetrieveMultipleRequest();
            req.Query = query;
            req.ReturnDynamicEntities = true;
            SSISLib.CRMService.RetrieveMultipleResponse resp = serv.Execute(req) as SSISLib.CRMService.RetrieveMultipleResponse;

            foreach (SSISLib.CRMService.DynamicEntity ent in resp.BusinessEntityCollection.BusinessEntities)
            {
                result.Add(ent);
            }

            return result.ToArray();
        }

        /// <summary>
        /// Property accessor
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="propName"></param>
        /// <returns></returns>
        private object GetPropertyValue(SSISLib.CRMService.DynamicEntity entity, string propName)
        {
            string proplower = propName.ToLower();

            foreach (SSISLib.CRMService.Property prop in entity.Properties)
            {
                if (prop.Name == propName || prop.Name == proplower)
                {
                    System.Reflection.PropertyInfo pinf = prop.GetType().GetProperty("Value");
                    object propVal = null;

                    if (pinf != null)
                    {
                        propVal = pinf.GetValue(prop, new object[0]);

                        pinf = propVal.GetType().GetProperty("Value");

                        if (pinf != null)
                        {
                            propVal = pinf.GetValue(propVal, new object[0]);
                        }
                    }

                    return propVal;
                }
            }

            return null;
        }

        /// <summary>
        /// Format conversion helper
        /// </summary>
        /// <param name="bindings"></param>
        /// <returns></returns>
        private EntityBinding ConvertToEntityBinding(BindingDefn[] bindings, Relationship[] relationships)
        {
            EntityBinding binding = new EntityBinding();

            if (bindings.Length > 0)
            {
                // convert to hierarchical format...
                List<BindingAttribute> bAttrs = new List<BindingAttribute>();

                foreach (BindingDefn bd in bindings)
                {
                    if (bd.entityPath == bindings[0].entityPath)
                    {
                        BindingAttribute bAttr = new BindingAttribute(bd);
                        bAttrs.Add(bAttr);
                    }
                    else
                    {
                        break;
                    }
                }

                binding.attributes = bAttrs.ToArray();
                binding.entityName = bindings[0].entityName;

                // now process relationships
                List<EntityRelationship> entityRelationships = new List<EntityRelationship>();

                System.Collections.Hashtable seenRelationship = new System.Collections.Hashtable();

                foreach (BindingDefn bd in bindings)
                {
                    if (bd.entityPath.StartsWith(bindings[0].entityName + "/") && seenRelationship[bd.ImmediateChildOf(bindings[0].entityName)] == null)
                    {
                        seenRelationship[bd.ImmediateChildOf(bindings[0].entityName)] = 1;

                        // add relationship...
                        EntityRelationship er = AddRelationship(bd.entityPath, bindings, relationships);
                        entityRelationships.Add(er);
                    }
                }

                binding.relationships = entityRelationships.ToArray();
            }

            return binding;
        }

        /// <summary>
        /// Create a hierarchical relationship...
        /// </summary>
        /// <param name="bindingDefn"></param>
        /// <returns></returns>
        private EntityRelationship AddRelationship(string entityPath, BindingDefn[] bindings, Relationship[] relationships)
        {
            EntityRelationship result = new EntityRelationship();

            // first attributes
            // convert to hierarchical format...
            List<BindingAttribute> bAttrs = new List<BindingAttribute>();

            bool bDoneInit = false;

            foreach (BindingDefn bd in bindings)
            {
                if (!bDoneInit && (bd.entityPath == entityPath || bd.entityPath.StartsWith(entityPath + "/")))
                {
                    bDoneInit = true;
                    string relationshipId = bd.relationshipId;

                    foreach (Relationship rel in relationships)
                    {
                        if (rel.relationshipId == relationshipId)
                        {
                            foreach (System.Reflection.FieldInfo finf in rel.GetType().GetFields())
                            {
                                finf.SetValue(result, finf.GetValue(rel));
                            }

                            result.entityName = (rel.relatedEntity);
                            break;
                        }
                    }
                }

                if (bd.entityPath == entityPath)
                {
                    BindingAttribute bAttr = new BindingAttribute(bd);
                    bAttrs.Add(bAttr);
                }
            }

            result.attributes = bAttrs.ToArray();

            // then relationships
            List<EntityRelationship> entityRelationships = new List<EntityRelationship>();

            System.Collections.Hashtable seenRelationship = new System.Collections.Hashtable();

            foreach (BindingDefn bd in bindings)
            {
                if (bd.entityPath.StartsWith(entityPath + "/") && seenRelationship[bd.ImmediateChildOf(entityPath)] == null)
                {
                    seenRelationship[bd.ImmediateChildOf(entityPath)] = 1;

                    // add relationship...
                    EntityRelationship er = AddRelationship(bd.ImmediateChildOf(entityPath), bindings, relationships);
                    entityRelationships.Add(er);
                }
            }

            result.relationships = entityRelationships.ToArray();
            return result;
        }

        /// <summary>
        /// Binding processing function
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="entityDefn"></param>
        /// <param name="thisRows"></param>
        private void ProcessObject(SSISLib.CRMService.DynamicEntity entity, IAttributeContainer entityDefn, List<List<KeyValuePair<string, object>>> thisRows)
        {
            // add attributes...
            foreach (SSISLib.CRMService.Property prop in entity.Properties)
            {
                foreach (BindingAttribute attr in entityDefn.attributes)
                {
                    if (attr.name.Length == prop.Name.Length && string.Compare(attr.name, prop.Name, true) == 0)
                    {
                        object propVal = GetPropertyValue(entity, prop.Name);

                        foreach (List<KeyValuePair<string, object>> thisRow in thisRows)
                        {
                            thisRow.Add(new KeyValuePair<string, object>(attr.targetName, propVal));
                        }

                        break;
                    }
                }
            }


            // iterate relationships
            foreach (EntityRelationship rel in entityDefn.relationships)
            {
                SSISLib.CRMService.BusinessEntity[] relatedEntities = GetRelatedEntities(entity, rel);

                if (rel.innerJoin && relatedEntities.Length == 0)
                {
                    thisRows.Clear();
                }
                else
                {
                    List<List<KeyValuePair<string, object>>> newRows = new List<List<KeyValuePair<string, object>>>();

                    foreach (SSISLib.CRMService.DynamicEntity relEntity in relatedEntities)
                    {
                        List<List<KeyValuePair<string, object>>> thisNewRows = new List<List<KeyValuePair<string, object>>>();

                        foreach (List<KeyValuePair<string, object>> thisRow in thisRows)
                        {
                            List<KeyValuePair<string, object>> newRow = new List<KeyValuePair<string, object>>();
                            newRow.AddRange(thisRow);
                            thisNewRows.Add(newRow);
                        }

                        ProcessObject(relEntity, rel, thisNewRows);
                        newRows.AddRange(thisNewRows);
                    }

                    thisRows.Clear();
                    thisRows.AddRange(newRows);
                }
            }
        }

        /// <summary>
        /// Binding processing function
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="entityDefn"></param>
        /// <param name="thisRows"></param>
        private void ProcessObject(SSISLib.CRMService.DynamicEntity entity, IAttributeContainer entityDefn, System.Xml.XmlElement element)
        {
            // add attributes...
            System.Xml.XmlAttribute idattr = element.OwnerDocument.CreateAttribute("ID");
            idattr.Value = this.GetEntityId(entity);
            element.Attributes.Append(idattr);

            foreach (SSISLib.CRMService.Property prop in entity.Properties)
            {
                foreach (BindingAttribute attr in entityDefn.attributes)
                {
                    if (attr.name.Length == prop.Name.Length && string.Compare(attr.name, prop.Name, true) == 0)
                    {
                        object propVal = GetPropertyValue(entity, prop.Name);

                        System.Xml.XmlAttribute xattr = element.OwnerDocument.CreateAttribute(attr.targetName);
                        xattr.Value = propVal != null ? propVal.ToString() : ""; 
                        element.Attributes.Append(xattr);

                        break;
                    }
                }
            }

            // iterate relationships
            foreach (EntityRelationship rel in entityDefn.relationships)
            {
                SSISLib.CRMService.BusinessEntity[] relatedEntities = GetRelatedEntities(entity, rel);

                if (rel.innerJoin && relatedEntities.Length == 0)
                {
                    element.Attributes.RemoveAll();

                    // delete all children
                    System.Collections.ArrayList toDelChildren = new System.Collections.ArrayList();

                    foreach (System.Xml.XmlNode node in element.ChildNodes)
                    {
                        toDelChildren.Add(node);
                    }

                    foreach (System.Xml.XmlNode node in toDelChildren)
                    {
                        element.RemoveChild(node);
                    }
                }
                else
                {
                    foreach (SSISLib.CRMService.DynamicEntity relEntity in relatedEntities)
                    {
                        System.Xml.XmlElement subElement = element.OwnerDocument.CreateElement(relEntity.Name);
                        ProcessObject(relEntity, rel, subElement);
                    }
                }
            }
        }

        public enum WriteMode
        {
            Insert=0,
            Update,
            Delete,
            Upsert,
            UpdateAllChildren,
            UpsertAllChildren
        }

        /// <summary>
        /// Static interface
        /// </summary>
        /// <param name="properties"><param>
        /// <param name="data"></param>
        public static void WriteData(System.Collections.Hashtable properties, System.Xml.XmlDocument data, BlueIntegrator.DAL.DALBase dal)
        {
            string url = properties["SPURL"] as string;
            string user = properties["SPUser"] as string;
            string fields = properties["SPRelevantColumns"] as string ?? "";
            string org = properties["SPOrg"] as string;

            string pwd = BlueIntegrator.Helpers.Encryption.DecryptString(properties["SPPwd"] as string ?? "", "CRMConfig");
            int timeOut = SafeGetInt(properties["SPTimeout"], 60);
            
            CRMAPI crm = new CRMAPI(url, org, user, pwd, timeOut, dal);

            List<string> scopeEntities = new List<string>();
            List<string> seenEntities = new List<string>();

            List<BindingDefn> bindings = new List<BindingDefn>();
            List<Relationship> relationships = new List<Relationship>();

            int nRelBindings = SafeGetInt(properties["NumRelBindings"]);
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bf = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            bf.AssemblyFormat = System.Runtime.Serialization.Formatters.FormatterAssemblyStyle.Simple;
            bf.TypeFormat = System.Runtime.Serialization.Formatters.FormatterTypeStyle.TypesWhenNeeded;

            for (int i = 0; i < nRelBindings; i++)
            {
                string bindingInfo = properties["RelBinding:" + i] as string;

                if (bindingInfo != null)
                {
                    System.IO.Compression.GZipStream unzipper = new System.IO.Compression.GZipStream(new System.IO.MemoryStream(System.Convert.FromBase64String(bindingInfo)), System.IO.Compression.CompressionMode.Decompress, true);
                    CRMAPI.Relationship r = (CRMAPI.Relationship)bf.Deserialize(unzipper);
                    relationships.Add(r);
                }
            }

            int nBindings = SafeGetInt(properties["NumTBindings"]);

            for (int i = 0; i < nBindings; i++)
            {
                string bindingInfo = properties["Binding:" + i] as string;

                if (bindingInfo != null)
                {
                    System.IO.Compression.GZipStream unzipper = new System.IO.Compression.GZipStream(new System.IO.MemoryStream(System.Convert.FromBase64String(bindingInfo)), System.IO.Compression.CompressionMode.Decompress, true);
                    CRMAPI.BindingDefn b = (CRMAPI.BindingDefn)bf.Deserialize(unzipper);
                    b.filterValue = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, b.filterValue);
                    bindings.Add(b);
                }
            }

            WriteMode mode = (CRMAPI.WriteMode)SafeGetInt(properties["SPMode"]); 
            crm.WriteData(data, bindings.ToArray(), relationships.ToArray(), mode, scopeEntities, seenEntities);
        }

        /// <summary>
        /// Writes data to CRM, Xml variant
        /// </summary>
        /// <param name="data"></param>
        /// <param name="bindings"></param>
        /// <param name="relationships"></param>
        /// <param name="mode"></param>
        /// <param name="scopeEntities"></param>
        /// <param name="seenEntities"></param>
        public void WriteData(System.Xml.XmlDocument data, BindingDefn[] bindings, Relationship[] relationships, WriteMode mode, List<string> scopeEntities, List<string> seenEntities)
        {
            try
            {
                try
                {
                    System.Array.Sort(bindings);
                    EntityBinding binding = ConvertToEntityBinding(bindings, relationships);
                    ProcessUpdateObject(null, data.DocumentElement, binding, mode, scopeEntities, seenEntities);
                }
                catch (System.Web.Services.Protocols.SoapException exc)
                {
                    System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                    throw rethrownExc;
                }
            }
            catch (System.Exception exc0)
            {
                System.Text.StringBuilder sb = new StringBuilder();
                sb.Append("Bindings : ");
                int n = 0;

                foreach (BindingDefn bd in bindings)
                {
                    if (n > 0)
                    {
                        sb.Append(", ");
                    }

                    sb.Append(bd.attributeDisplayPath ?? "");
                    sb.Append(" ");
                    sb.Append(bd.mappingType.ToString());
                    sb.Append(" ");
                    sb.Append(bd.sourceMapping ?? "");
                    sb.Append(" (");
                    sb.Append(bd.filterValue ?? "");
                    sb.Append(")");
                }

                if (exc0 is System.Web.Services.Protocols.SoapException)
                    throw new System.Web.Services.Protocols.SoapException(exc0.Message + System.Environment.NewLine + sb.ToString(), (exc0 as System.Web.Services.Protocols.SoapException).Code);
                else
                    throw new System.Exception(exc0.Message + System.Environment.NewLine + sb.ToString());
            }
        }

        /// <summary>
        /// Write data back to CRM
        /// </summary>
        /// <param name="data"></param>
        /// <param name="bindings"></param>
        /// <param name="mode"></param>
        public void WriteData(System.Collections.Generic.KeyValuePair<string, object>[] data, BindingDefn[] bindings, Relationship[] relationships, WriteMode mode, List<string> scopeEntities, List<string> seenEntities)
        {
            try
            {
                try
                {
                    System.Array.Sort(bindings);
                    EntityBinding binding = ConvertToEntityBinding(bindings, relationships);
                    ProcessUpdateObject(null, data, binding, mode, scopeEntities, seenEntities);
                }
                catch (System.Web.Services.Protocols.SoapException exc)
                {
                    System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                    throw rethrownExc;
                }
            }
            catch (System.Exception exc0)
            {
                System.Text.StringBuilder sb = new StringBuilder();
                sb.Append("Bindings : ");
                int n = 0;

                foreach (BindingDefn bd in bindings)
                {
                    if (n > 0)
                    {
                        sb.Append(", ");
                    }

                    sb.Append(bd.attributeDisplayPath ?? "");
                    sb.Append(" ");
                    sb.Append(bd.mappingType.ToString());
                    sb.Append(" ");
                    sb.Append(bd.sourceMapping ?? "");
                    sb.Append(" (");
                    sb.Append(bd.filterValue ?? "");
                    sb.Append(")");
                }

                if (exc0 is System.Web.Services.Protocols.SoapException)
                    throw new System.Web.Services.Protocols.SoapException(exc0.Message + System.Environment.NewLine + sb.ToString(), (exc0 as System.Web.Services.Protocols.SoapException).Code);
                else
                    throw new System.Exception(exc0.Message + System.Environment.NewLine + sb.ToString());
            }
        }

        /// <summary>
        /// Hierarchical update function
        /// </summary>
        /// <param name="rootEntity"></param>
        /// <param name="data"></param>
        /// <param name="binding"></param>
        /// <param name="bindings"></param>
        /// <param name="mode"></param>
        private void ProcessUpdateObject(SSISLib.CRMService.DynamicEntity rootEntity, System.Xml.XmlElement dataScope, IAttributeContainer binding, WriteMode _mode, List<string> scopeEntities, List<string> seenEntities)
        {
            SSISLib.CRMService.DynamicEntity newEntity = null;

            string name = GetEntityName(binding.entityName);

            foreach (System.Xml.XmlNode record in dataScope.ChildNodes)
            {
                WriteMode mode = _mode;
                System.Collections.Hashtable keys = new System.Collections.Hashtable();

                if (record is System.Xml.XmlElement && record.Name == name)
                {
                    List<KeyValuePair<string, object>> dataList = new List<KeyValuePair<string, object>>();

                    foreach (XmlAttribute attr in record.Attributes)
                    {
                        dataList.Add(new KeyValuePair<string, object>(attr.Name, attr.Value));
                    }

                    KeyValuePair<string, object>[] data = dataList.ToArray();

                    // maybe override mode?
                    // is there a Action record
                    foreach (System.Xml.XmlNode child in record.ChildNodes)
                    {
                        if (child is XmlElement && child.Name == "Action")
                        {
                            // capture action type...
                            XmlAttribute actionType = child.Attributes["ActionType"];

                            if (actionType != null)
                            {
                                if (actionType.Value.ToLower() == "insert")
                                {
                                    mode = WriteMode.Insert;
                                }
                                else if (actionType.Value.ToLower() == "update")
                                {
                                    mode = WriteMode.Update;
                                }
                                else if (actionType.Value.ToLower() == "update_allchildren")
                                {
                                    mode = WriteMode.UpdateAllChildren;
                                }
                                else if (actionType.Value.ToLower() == "delete")
                                {
                                    mode = WriteMode.Delete;
                                }
                                else if (actionType.Value.ToLower() == "upsert")
                                {
                                    mode = WriteMode.Upsert;
                                }
                                else if (actionType.Value.ToLower() == "upsert_allchildren")
                                {
                                    mode = WriteMode.UpsertAllChildren;
                                }
                                else
                                {
                                    throw new ApplicationException("Invalid ActionType : " + actionType.Value);
                                }
                            }

                            foreach (System.Xml.XmlNode child2 in child.ChildNodes)
                            {
                                if (child2 is XmlElement && child2.Name == "Key")
                                {
                                    foreach (XmlAttribute a in child2.Attributes)
                                    {
                                        keys[a.Name] = a.Value;
                                    }
                                }
                            }
                        }
                    }

                    if (mode == WriteMode.Insert)
                    {
                        // insert new entity
                        newEntity = CreateObject(rootEntity, binding, binding as EntityRelationship, binding.entityName, data);
                    }
                    else if (mode == WriteMode.Update || mode == WriteMode.UpdateAllChildren || mode == WriteMode.Upsert || mode == WriteMode.UpsertAllChildren)
                    {
                        // update
                        newEntity = FindEntity(rootEntity, binding, binding as EntityRelationship, binding.entityName, data, mode == WriteMode.UpdateAllChildren || mode == WriteMode.UpsertAllChildren ? scopeEntities : null, seenEntities, keys);

                        if (newEntity == null)
                        {
                            if (mode == WriteMode.Upsert || mode == WriteMode.UpsertAllChildren)
                            {
                                newEntity = CreateObject(rootEntity, binding, binding as EntityRelationship, binding.entityName, data);
                            }
                            else
                            {
                                // do nothing...
                            }
                        }
                        else
                        {
                            // apply bindings...
                            // write data back
                            List<CRMService.Property> diffProps = new List<SSISLib.CRMService.Property>();
                            diffProps.Add(CreateCRMProperty(binding.entityName, CRMMetaService.AttributeType.PrimaryKey, GetEntityIdFieldName(newEntity), GetEntityId(newEntity)));

                            foreach (KeyValuePair<string, object> dataItem in data)
                            {
                                // find corresponding binding
                                foreach (BindingAttribute bd in binding.attributes)
                                {
                                    if (bd.name == dataItem.Key && bd.mappingType == BindingDefn.MappingType.Value)
                                    {
                                        object currVal = GetPropertyValue(newEntity, bd.name);

                                        bool bDifferent = (currVal == null && dataItem.Value != null) || (currVal != null && dataItem.Value == null) || (currVal != null && currVal.ToString() != dataItem.Value.ToString());

                                        if (bDifferent)
                                            diffProps.Add(CreateCRMProperty(binding.entityName, bd.crmType, bd.name.ToLower(), dataItem.Value));

                                        break;
                                    }
                                }
                            }

                            if (diffProps.Count >= 2)
                            {
                                CRMService.TargetUpdateDynamic target = new SSISLib.CRMService.TargetUpdateDynamic();
                                target.Entity = newEntity;
                                target.Entity.Properties = diffProps.ToArray();
                                CRMService.UpdateRequest req = new SSISLib.CRMService.UpdateRequest();
                                req.Target = target;
                                GetCRMService().Execute(req);
                            }
                        }
                    }
                    else if (mode == WriteMode.Delete)
                    {
                        // update
                        newEntity = FindEntity(rootEntity, binding, binding as EntityRelationship, binding.entityName, data, mode == WriteMode.UpdateAllChildren || mode == WriteMode.UpsertAllChildren ? scopeEntities : null, seenEntities, keys);
                    }

                    // now apply relationships
                    if (newEntity != null)
                    {
                        seenEntities.Add(newEntity.Name + ":" + GetEntityId(newEntity));

                        foreach (EntityRelationship rel in binding.relationships)
                        {
                            ProcessUpdateObject(newEntity, record as XmlElement, rel, mode, scopeEntities, seenEntities);
                        }
                    }

                    // apply deletes at tail end
                    if (mode == WriteMode.Delete)
                    {
                        if (newEntity != null)
                            DeleteObject(newEntity.Name + ":" + GetEntityId(newEntity));
                    }
                }
            }
        }

        /// <summary>
        /// Hierarchical update function
        /// </summary>
        /// <param name="rootEntity"></param>
        /// <param name="data"></param>
        /// <param name="binding"></param>
        /// <param name="bindings"></param>
        /// <param name="mode"></param>
        private void ProcessUpdateObject(SSISLib.CRMService.DynamicEntity rootEntity, System.Collections.Generic.KeyValuePair<string, object>[] data, IAttributeContainer binding, WriteMode mode, List<string> scopeEntities, List<string> seenEntities)
        {
            SSISLib.CRMService.DynamicEntity newEntity = null;

            if (mode == WriteMode.Insert)
            {
                // insert new entity
                newEntity = CreateObject(rootEntity, binding, binding as EntityRelationship, binding.entityName, data);
            }
            else if (mode == WriteMode.Update || mode == WriteMode.UpdateAllChildren || mode == WriteMode.Upsert || mode == WriteMode.UpsertAllChildren)
            {
                // update
                newEntity = FindEntity(rootEntity, binding, binding as EntityRelationship, binding.entityName, data, scopeEntities, seenEntities);

                if (newEntity == null)
                {
                    if (mode == WriteMode.Upsert || mode == WriteMode.UpsertAllChildren)
                    {
                        newEntity = CreateObject(rootEntity, binding, binding as EntityRelationship, binding.entityName, data);
                    }
                    else
                    {
                        // do nothing...
                    }
                }
                else
                {
                    // apply bindings...
                    // write data back
                    List<CRMService.Property> diffProps = new List<SSISLib.CRMService.Property>();
                    diffProps.Add(CreateCRMProperty(binding.entityName, CRMMetaService.AttributeType.PrimaryKey, GetEntityIdFieldName(newEntity), GetEntityId(newEntity)));

                    foreach (KeyValuePair<string, object> dataItem in data)
                    {
                        // find corresponding binding
                        foreach (BindingAttribute bd in binding.attributes)
                        {
                            if (bd.name == dataItem.Key && bd.mappingType == BindingDefn.MappingType.Value)
                            {
                                object currVal = GetPropertyValue(newEntity, bd.name);

                                bool bDifferent = (currVal == null && dataItem.Value != null) || (currVal != null && dataItem.Value == null) || (currVal != null && currVal.ToString() != dataItem.Value.ToString());

                                if (bDifferent)
                                    diffProps.Add(CreateCRMProperty(binding.entityName, bd.crmType, bd.name.ToLower(), dataItem.Value));

                                break;
                            }
                        }
                    }

                    if (diffProps.Count >= 2)
                    {
                        CRMService.TargetUpdateDynamic target = new SSISLib.CRMService.TargetUpdateDynamic();
                        target.Entity = newEntity;
                        target.Entity.Properties = diffProps.ToArray();
                        CRMService.UpdateRequest req = new SSISLib.CRMService.UpdateRequest();
                        req.Target = target;
                        GetCRMService().Execute(req);
                    }
                }
            }

            // now apply relationships
            if (newEntity != null)
            {
                seenEntities.Add(newEntity.Name + ":" + GetEntityId(newEntity));

                foreach (EntityRelationship rel in binding.relationships)
                {
                    ProcessUpdateObject(newEntity, data, rel, mode, scopeEntities, seenEntities);
                }
            }

            // apply deletes at tail end
            if (mode == WriteMode.Delete)
            {
                DeleteObject(rootEntity.Name + ":" + GetEntityId(rootEntity));
            }
        }

        /// <summary>
        /// Find matching entity
        /// </summary>
        /// <param name="rootEntity"></param>
        /// <param name="scope"></param>
        /// <param name="rel"></param>
        /// <param name="entityName"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        SSISLib.CRMService.DynamicEntity FindEntity(SSISLib.CRMService.DynamicEntity rootEntity, IAttributeContainer scope, EntityRelationship rel, string entityName, System.Collections.Generic.KeyValuePair<string, object>[] data, List<string> scopeEntities, List<string> seenEntities, System.Collections.Hashtable additionalFilters = null)
        {
            // execute query for entities of the given type
            SSISLib.CRMService.DynamicEntity result = null;
            SSISLib.CRMService.CrmService serv = GetCRMService();

            // now get relationship
            List<SSISLib.CRMService.ConditionExpression> conditions = new List<SSISLib.CRMService.ConditionExpression>();
            SSISLib.CRMService.QueryExpression query = new SSISLib.CRMService.QueryExpression();
            query.EntityName = this.GetEntityName(rel != null ? rel.entityName : scope.entityName).ToLower();
            query.ColumnSet = new SSISLib.CRMService.AllColumns();

            if (rootEntity != null)
            {
                // add relationship critera
                query.Criteria = new SSISLib.CRMService.FilterExpression();
                query.Criteria.FilterOperator = SSISLib.CRMService.LogicalOperator.And;

                SSISLib.CRMService.ConditionExpression expr = new SSISLib.CRMService.ConditionExpression();
                expr.AttributeName = rel.relatedAttribute.ToLower();
                expr.Operator = SSISLib.CRMService.ConditionOperator.Equal;

                // get attribute value
                object currentVal = null;

                foreach (SSISLib.CRMService.Property prop in rootEntity.Properties)
                {
                    if (prop.Name == rel.currentAttribute)
                    {
                        System.Reflection.PropertyInfo pinf = prop.GetType().GetProperty("Value");
                        object propVal = null;

                        if (pinf != null)
                        {
                            propVal = pinf.GetValue(prop, new object[0]);

                            pinf = propVal.GetType().GetProperty("Value");

                            if (pinf != null)
                            {
                                propVal = pinf.GetValue(propVal, new object[0]);
                            }
                        }

                        currentVal = propVal;
                        break;
                    }
                }

                expr.Values = new object[] { currentVal };
                conditions.Add(expr);
            }

            if (additionalFilters != null && additionalFilters.ContainsKey("ID"))
            {
                SSISLib.CRMService.ConditionExpression expr = new SSISLib.CRMService.ConditionExpression();
                expr.AttributeName = query.EntityName.ToLower() + "id";
                expr.Operator = CRMService.ConditionOperator.Equal;
                expr.Values = new object[] { new System.Guid (BlueIntegrator.Helpers.Conversion.SafeGetString(additionalFilters["ID"])) };
                conditions.Add(expr);
            }

            foreach (BindingAttribute ba in scope.attributes)
            {
                if (ba.mappingType == BindingDefn.MappingType.Key || (int)ba.mappingType >= 10)
                {
                    SSISLib.CRMService.ConditionExpression expr = new SSISLib.CRMService.ConditionExpression();
                    expr.AttributeName = ba.name.ToLower();
                    expr.Operator = MapCRMOperator(ba.mappingType);
                    expr.Values = new object[] { ConvertToAppropriateType(query.EntityName, ba.filterValue, ba.crmType) };
                    conditions.Add(expr);
                }

                if (additionalFilters != null && additionalFilters.ContainsKey(ba.name))
                {
                    SSISLib.CRMService.ConditionExpression expr = new SSISLib.CRMService.ConditionExpression();
                    expr.AttributeName = ba.name.ToLower();
                    expr.Operator = CRMService.ConditionOperator.Equal;
                    expr.Values = new object[] { ConvertToAppropriateType(query.EntityName, additionalFilters[ba.name], ba.crmType) };
                    conditions.Add(expr);
                }
            }

            if (query.Criteria == null)
            {
                query.Criteria = new SSISLib.CRMService.FilterExpression();
                query.Criteria.FilterOperator = SSISLib.CRMService.LogicalOperator.And;
            }
            
            query.Criteria.Conditions = conditions.ToArray();

            if (conditions.Count == 0)
                return null;

            SSISLib.CRMService.RetrieveMultipleRequest req = new SSISLib.CRMService.RetrieveMultipleRequest();
            req.Query = query;
            req.ReturnDynamicEntities = true;
            SSISLib.CRMService.RetrieveMultipleResponse resp = serv.Execute(req) as SSISLib.CRMService.RetrieveMultipleResponse;

            foreach (SSISLib.CRMService.DynamicEntity ent in resp.BusinessEntityCollection.BusinessEntities)
            {
                if (result == null && (resp.BusinessEntityCollection.BusinessEntities.Length == 1 || seenEntities.IndexOf(ent.Name + ":" + GetEntityId(ent)) < 0))
                {
                    result = ent;
                }

                if (scopeEntities != null)
                    scopeEntities.Add(ent.Name + ":" + GetEntityId(ent));
            }

            return result;
        }

        /// <summary>
        /// Object creation
        /// </summary>
        /// <param name="rootEntity"></param>
        /// <param name="rel"></param>
        /// <param name="entityType"></param>
        /// <param name="bindings"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        SSISLib.CRMService.DynamicEntity CreateObject(SSISLib.CRMService.DynamicEntity rootEntity, IAttributeContainer scope, EntityRelationship rel, string entityName, System.Collections.Generic.KeyValuePair<string, object>[] data)
        {
            SSISLib.CRMService.DynamicEntity dent = new SSISLib.CRMService.DynamicEntity();
            dent.Name = this.GetEntityName(entityName).ToLower();

            List<SSISLib.CRMService.Property> properties = new List<SSISLib.CRMService.Property>();

            foreach (BindingAttribute attr in scope.attributes)
            {
                // set value...
                object val = attr.filterValue;

                foreach (System.Collections.Generic.KeyValuePair<string, object> d in data)
                {
                    if (d.Key.ToLower() == attr.name.ToLower())
                    {
                        val = d.Value; break;
                    }
                }

                properties.Add(CreateCRMProperty(dent.Name, attr.crmType, attr.name.ToLower(), val));
            }

            // add relation properties
            if (rel != null)
            {
                properties.Add(CreateCRMProperty(dent.Name, GetAttributeCRMType(rel.relatedEntity, rel.relatedAttribute), rel.relatedAttribute, GetPropertyValue(rootEntity, rel.currentAttribute)));
            }

            dent.Properties = properties.ToArray();

            // execute
            SSISLib.CRMService.TargetCreateDynamic target = new SSISLib.CRMService.TargetCreateDynamic();
            target.Entity = dent;
            SSISLib.CRMService.CreateRequest cr = new SSISLib.CRMService.CreateRequest();
            cr.Target = target;
            string id = (GetCRMService().Execute(cr) as SSISLib.CRMService.CreateResponse).id.ToString();

            // now reload entity
            SSISLib.CRMService.TargetRetrieveDynamic target2 = new SSISLib.CRMService.TargetRetrieveDynamic();
            target2.EntityName = dent.Name;
            target2.EntityId = new Guid(id);

            SSISLib.CRMService.RetrieveRequest req = new SSISLib.CRMService.RetrieveRequest();
            req.ColumnSet = new SSISLib.CRMService.AllColumns();
            req.ReturnDynamicEntities = true;
            req.Target = target2;

            SSISLib.CRMService.RetrieveResponse resp = GetCRMService().Execute(req) as SSISLib.CRMService.RetrieveResponse;
            return resp.BusinessEntity as SSISLib.CRMService.DynamicEntity;
        }

        /// <summary>
        /// Delete children not seen
        /// </summary>
        /// <param name="scopeEntities"></param>
        /// <param name="seenEntities"></param>
        public void DeleteOrphanedChildren(string[] scopeEntities, string[] seenEntities)
        {
            foreach (string entity in scopeEntities)
            {
                if (System.Array.IndexOf(seenEntities, entity) < 0)
                {
                    DeleteObject(entity);
                }
            }
        }

        /// <summary>
        /// Object deletion
        /// </summary>
        /// <param name="id"></param>
        private void DeleteObject(string id)
        {
            int n = id.IndexOf(":");

            if (n > 0)
                GetCRMService().Delete(id.Substring(0, n), new Guid(id.Substring(n + 1)));
        }

        /// <summary>
        /// Property creation
        /// </summary>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <param name="val"></param>
        /// <returns></returns>
        private CRMService.Property CreateCRMProperty(string entityName, object type, string name, object val)
        {
            switch ((CRMMetaService.AttributeType)type)
            {
                case SSISLib.CRMMetaService.AttributeType.Boolean:
                    CRMService.CrmBooleanProperty bprop = new SSISLib.CRMService.CrmBooleanProperty();
                    bprop.Name = name;
                    bprop.Value = new SSISLib.CRMService.CrmBoolean();

                    if (val == null || val == System.DBNull.Value)
                        bprop.Value.IsNullSpecified = bprop.Value.IsNull = true;
                    else
                        bprop.Value.Value = (val as string == "1") || System.Convert.ToBoolean(val);
                    return bprop;

                case SSISLib.CRMMetaService.AttributeType.DateTime:
                    CRMService.CrmDateTimeProperty dprop = new SSISLib.CRMService.CrmDateTimeProperty();
                    dprop.Name = name; dprop.Value = new SSISLib.CRMService.CrmDateTime();

                    if (val == null || val == System.DBNull.Value)
                    {
                        dprop.Value.IsNullSpecified = dprop.Value.IsNull = true;
                    }
                    else
                    {
                        SSISLib.CRMService.CrmDateTime crmVal = ConvertToCRMDateTime(System.Convert.ToDateTime(val));
                        dprop.Value.Value = crmVal.Value;
                        dprop.Value.date = crmVal.date;
                        dprop.Value.time = crmVal.time;
                    }
                    return dprop;

                case SSISLib.CRMMetaService.AttributeType.Decimal:
                    CRMService.CrmDecimalProperty decprop = new SSISLib.CRMService.CrmDecimalProperty();
                    decprop.Name = name;
                    decprop.Value = new SSISLib.CRMService.CrmDecimal();

                    if (val == null || val == System.DBNull.Value)
                        decprop.Value.IsNullSpecified = decprop.Value.IsNull = true;
                    else
                        decprop.Value.Value = System.Convert.ToDecimal(val);
                    return decprop;

                case SSISLib.CRMMetaService.AttributeType.Float:
                    CRMService.CrmFloatProperty fltprop = new SSISLib.CRMService.CrmFloatProperty();
                    fltprop.Name = name;
                    fltprop.Value = new SSISLib.CRMService.CrmFloat();

                    if (val == null || val == System.DBNull.Value)
                        fltprop.Value.IsNullSpecified = fltprop.Value.IsNull = true;
                    else
                        fltprop.Value.Value = System.Convert.ToSingle(val);
                    return fltprop;

                case SSISLib.CRMMetaService.AttributeType.Integer:
                    CRMService.CrmNumberProperty numprop = new SSISLib.CRMService.CrmNumberProperty();
                    numprop.Name = name;
                    numprop.Value = new SSISLib.CRMService.CrmNumber();

                    if (val == null || val == System.DBNull.Value)
                        numprop.Value.IsNullSpecified = numprop.Value.IsNull = true;
                    else
                        numprop.Value.Value = System.Convert.ToInt32(val);
                    return numprop;

                case SSISLib.CRMMetaService.AttributeType.PrimaryKey:
                    CRMService.KeyProperty keyprop = new SSISLib.CRMService.KeyProperty();
                    keyprop.Name = name;
                    keyprop.Value = new SSISLib.CRMService.Key();

                    if (val == null || val == System.DBNull.Value)
                        keyprop.Value = null;
                    else
                        keyprop.Value.Value = new System.Guid(System.Convert.ToString(val != null ? val : ""));

                    return keyprop;

                case SSISLib.CRMMetaService.AttributeType.Money:
                    CRMService.CrmMoneyProperty monprop = new SSISLib.CRMService.CrmMoneyProperty();
                    monprop.Name = name;
                    monprop.Value = new SSISLib.CRMService.CrmMoney();

                    if (val == null || val == System.DBNull.Value)
                        monprop.Value.IsNullSpecified = monprop.Value.IsNull = true;
                    else
                        monprop.Value.Value = System.Convert.ToDecimal(val);
                    return monprop;

                case SSISLib.CRMMetaService.AttributeType.String:
                    CRMService.StringProperty strprop = new SSISLib.CRMService.StringProperty();
                    strprop.Name = name;
                    strprop.Value = val != null ? System.Convert.ToString(val) : null;
                    return strprop;

                case SSISLib.CRMMetaService.AttributeType.UniqueIdentifier:
                    CRMService.UniqueIdentifierProperty uiprop = new SSISLib.CRMService.UniqueIdentifierProperty();
                    uiprop.Name = name;
                    uiprop.Value = new SSISLib.CRMService.UniqueIdentifier();

                    if (val == null || val == System.DBNull.Value || (val is System.Guid && ((System.Guid)val) == System.Guid.Empty))
                        uiprop.Value.IsNullSpecified = uiprop.Value.IsNull = true;
                    else
                        uiprop.Value.Value = new System.Guid(System.Convert.ToString(val));

                    return uiprop;

                case SSISLib.CRMMetaService.AttributeType.Picklist:
                    CRMService.PicklistProperty plprop = new SSISLib.CRMService.PicklistProperty();
                    plprop.Name = name;
                    plprop.Value = new SSISLib.CRMService.Picklist();

                    int iPicklist = -1;

                    if (val == null || val == System.DBNull.Value || val as string == "")
                    {
                        plprop.Value.IsNullSpecified = plprop.Value.IsNull = true;
                    }
                    else if (int.TryParse(System.Convert.ToString(val), out iPicklist))
                    {
                        plprop.Value.Value = iPicklist;
                    }
                    else
                    {
                        iPicklist = ResolvePickListValue (entityName, name, (System.Convert.ToString(val)));

                        if (iPicklist >= 0)
                        {
                            plprop.Value.Value = iPicklist;
                        }
                        else
                        {
                            throw new ApplicationException("Unable to resolve picklist value " + val + " on attribute " + entityName + "." + name);
                        }
                    }

                    return plprop;

                case SSISLib.CRMMetaService.AttributeType.State:
                    CRMService.StateProperty stprop = new SSISLib.CRMService.StateProperty();
                    stprop.Name = name;

                    if (val == null || val == System.DBNull.Value)
                        stprop.Value = null;
                    else
                        stprop.Value = System.Convert.ToString(val);
                    return stprop;

                case SSISLib.CRMMetaService.AttributeType.Status:
                    CRMService.StatusProperty staprop = new SSISLib.CRMService.StatusProperty();
                    staprop.Name = name;
                    staprop.Value = new SSISLib.CRMService.Status();

                    if (val == null || val == System.DBNull.Value)
                        staprop.Value.IsNullSpecified = staprop.Value.IsNull = true;
                    else
                        staprop.Value.name = System.Convert.ToString(val);

                    return staprop;

                case SSISLib.CRMMetaService.AttributeType.Lookup:
                    CRMService.LookupProperty looprop = new SSISLib.CRMService.LookupProperty();
                    looprop.Name = name;
                    looprop.Value = new SSISLib.CRMService.Lookup();

                    if (val == null || val == System.DBNull.Value || (val is System.Guid && ((System.Guid)val) == System.Guid.Empty))
                        looprop.Value.IsNullSpecified = looprop.Value.IsNull = true;
                    else
                        looprop.Value.Value = new Guid(System.Convert.ToString(val));
                    return looprop;

                case SSISLib.CRMMetaService.AttributeType.Owner:
                    CRMService.OwnerProperty ownprop = new SSISLib.CRMService.OwnerProperty();
                    ownprop.Name = name;
                    ownprop.Value = new SSISLib.CRMService.Owner();

                    if (val == null || val == System.DBNull.Value || (val is System.Guid && ((System.Guid)val) == System.Guid.Empty))
                        ownprop.Value.IsNullSpecified = ownprop.Value.IsNull = true;
                    else
                        ownprop.Value.Value = new Guid(System.Convert.ToString(val));
                    return ownprop;

                case SSISLib.CRMMetaService.AttributeType.Customer:
                    CRMService.CustomerProperty custprop = new SSISLib.CRMService.CustomerProperty();
                    custprop.Name = name;
                    custprop.Value = new SSISLib.CRMService.Customer();
                    if (val == null || val == System.DBNull.Value || (val is System.Guid && ((System.Guid)val) == System.Guid.Empty))
                        custprop.Value.IsNullSpecified = custprop.Value.IsNull = true;
                    else
                        custprop.Value.Value = new Guid(System.Convert.ToString(val));
                    return custprop;
            }

            return null;
        }

        static public SSISLib.CRMService.CrmDateTime ConvertToCRMDateTime(DateTime dateTime) 
        {
            SSISLib.CRMService.CrmDateTime crmDateTime = new SSISLib.CRMService.CrmDateTime();   
            crmDateTime.date = dateTime.ToShortDateString();   
            crmDateTime.time = dateTime.ToShortTimeString();   
            TimeSpan offset = TimeZone.CurrentTimeZone.GetUtcOffset(dateTime);   
            string sOffset = string.Empty;   
            if (offset.Hours < 0)   
            {
                sOffset = "-" + (offset.Hours * -1).ToString().PadLeft(2, '0');
            }
            else
            {
                sOffset = "+" + offset.Hours.ToString().PadLeft(2, '0');
            }
            
            sOffset += offset.Minutes.ToString().PadLeft(2, '0');  
            
            crmDateTime.Value = dateTime.ToString(string.Format("yyyy-MM-ddTHH:mm:ss{0}", sOffset));  
            return crmDateTime;
        }        

        /// <summary>
        /// Picklist value resolution
        /// </summary>
        /// <param name="entityName"></param>
        /// <param name="name"></param>
        /// <param name="val"></param>
        /// <returns></returns>
        private int ResolvePickListValue(string entityName, string name, string val)
        {
            SSISLib.CRMMetaService.RetrieveAttributeRequest attributeRequest = new SSISLib.CRMMetaService.RetrieveAttributeRequest();  
            attributeRequest.EntityLogicalName = GetEntityName(entityName).ToLower();
            attributeRequest.LogicalName = name.ToLower();

            object cached = m_picklistCache[attributeRequest.EntityLogicalName + "." + name + "." + val];

            if (cached is int)
            {
                return (int)(cached);
            }
            
            SSISLib.CRMMetaService.RetrieveAttributeResponse attributeResponse =  (SSISLib.CRMMetaService.RetrieveAttributeResponse)GetMetaService().Execute(attributeRequest);  
            SSISLib.CRMMetaService.PicklistAttributeMetadata picklist = (SSISLib.CRMMetaService.PicklistAttributeMetadata)attributeResponse.AttributeMetadata;

            foreach (var option in picklist.Options)
            {
                // cache
                m_picklistCache[attributeRequest.EntityLogicalName + "." + name + "." + val] = option.Value;

                if (string.Compare(option.Label.UserLocLabel.Label, val, true) == 0)
                    return option.Value.Value;
            }

            return -1;
        }

        private System.Collections.Hashtable m_picklistCache = new System.Collections.Hashtable();

        /// <summary>
        /// Converts a value to CRM Type
        /// </summary>
        /// <param name="crmType"></param>
        /// <param name="val"></param>
        /// <returns></returns>
        object ConvertToAppropriateType(string entityType, object val, object crmType)
        {
            CRMService.Property prop = CreateCRMProperty(entityType, crmType, "Dummy", val);

            if (prop != null)
            {
                if (val != null)
                {
                    System.Reflection.PropertyInfo pinf = val.GetType().GetProperty("Value");

                    if (pinf != null)
                        val = pinf.GetValue(val, new object[0]);

                    if (val != null)
                    {
                        pinf = val.GetType().GetProperty("Value");

                        if (pinf != null)
                            val = pinf.GetValue(val, new object[0]);
                    }
                }

                return val;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// CRM Operator mapping
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        CRMService.ConditionOperator MapCRMOperator(BindingDefn.MappingType condition)
        {
            switch (condition)
            {
                case BindingDefn.MappingType.Condition_Equals:
                case BindingDefn.MappingType.Key:
                    return SSISLib.CRMService.ConditionOperator.Equal;

                case BindingDefn.MappingType.Condition_In:
                    return SSISLib.CRMService.ConditionOperator.In;

                case BindingDefn.MappingType.Condition_Between:
                    return SSISLib.CRMService.ConditionOperator.Between;

                case BindingDefn.MappingType.Condition_NotEquals:
                    return SSISLib.CRMService.ConditionOperator.NotEqual;

                case BindingDefn.MappingType.Condition_NotNull:
                    return SSISLib.CRMService.ConditionOperator.NotNull;

                case BindingDefn.MappingType.Condition_Null:
                    return SSISLib.CRMService.ConditionOperator.Null;

                case BindingDefn.MappingType.Condition_GT:
                    return SSISLib.CRMService.ConditionOperator.GreaterThan;

                case BindingDefn.MappingType.Condition_GTE:
                    return SSISLib.CRMService.ConditionOperator.GreaterEqual;

                case BindingDefn.MappingType.Condition_LT:
                    return SSISLib.CRMService.ConditionOperator.LessThan;

                case BindingDefn.MappingType.Condition_LTE:
                    return SSISLib.CRMService.ConditionOperator.LessEqual;

                case BindingDefn.MappingType.Condition_Like:
                    return SSISLib.CRMService.ConditionOperator.Like;

                case BindingDefn.MappingType.Condition_NotLike:
                    return SSISLib.CRMService.ConditionOperator.NotLike;

                case BindingDefn.MappingType.Condition_ThisYear:
                    return SSISLib.CRMService.ConditionOperator.ThisYear;

                case BindingDefn.MappingType.Condition_ThisMonth:
                    return SSISLib.CRMService.ConditionOperator.ThisMonth;

                case BindingDefn.MappingType.Condition_ThisWeek:
                    return SSISLib.CRMService.ConditionOperator.ThisWeek;

                case BindingDefn.MappingType.Condition_LastYear:
                    return SSISLib.CRMService.ConditionOperator.LastYear;

                case BindingDefn.MappingType.Condition_LastMonth:
                    return SSISLib.CRMService.ConditionOperator.LastMonth;

                case BindingDefn.MappingType.Condition_LastWeek:
                    return SSISLib.CRMService.ConditionOperator.LastWeek;

                case BindingDefn.MappingType.Condition_NextYear:
                    return SSISLib.CRMService.ConditionOperator.NextYear;

                case BindingDefn.MappingType.Condition_NextMonth:
                    return SSISLib.CRMService.ConditionOperator.NextMonth;

                case BindingDefn.MappingType.Condition_NextWeek:
                    return SSISLib.CRMService.ConditionOperator.NextWeek;
            }

            return SSISLib.CRMService.ConditionOperator.Equal;
        }

        #region Data Structure classes
        /// <summary>
        /// Attribute definition class
        /// </summary>
        [Serializable]
        public class Attribute : System.IComparable<Attribute>
        {
            public string name;
            public object crmType;
            public string ssisType;
            public bool required;

            #region IComparable<Attribute> Members

            public int CompareTo(Attribute other)
            {
                return name.CompareTo(other.name);
            }

            #endregion
        }

        /// <summary>
        /// Relationship class
        /// </summary>
        [Serializable]
        public class Relationship : System.IComparable<Relationship>
        {
            public string relationshipId, name;
            public RelationshipType type;
            public bool innerJoin;
            public string relatedEntity, relatedAttribute, currentEntity, currentAttribute;

            public enum RelationshipType
            {
                OneToMany, ManyToOne, ManyToMany
            }

            #region IComparable<Relationship> Members

            public int CompareTo(Relationship other)
            {
                return name.CompareTo(other.name);
            }

            #endregion
        }

        /// <summary>
        /// BindingDefn 
        /// </summary>
        [Serializable]
        public class BindingDefn : System.IComparable<BindingDefn>
        {
            public string attributePath;   //  entity/relationship.../@attribute, where relationship is relationship id 
            public string attributeDisplayPath;   //  displayed path
            public Attribute attributeDefn;
            public string sourceMapping = null;
            public string targetName = "";

            public enum MappingType
            {
                None=-1,
                Key = 0,
                Value,
                PassiveValue,
                Condition_Equals = 10,
                Condition_Like = 12,
                Condition_NotEquals = 13,
                Condition_NotLike = 14,
                Condition_LT = 15,
                Condition_GT = 16,
                Condition_LTE = 17,
                Condition_GTE = 18,
                Condition_ThisYear = 19,
                Condition_LastYear = 20,
                Condition_NextYear = 21,
                Condition_ThisMonth = 22,
                Condition_LastMonth = 23,
                Condition_NextMonth = 24,
                Condition_ThisWeek = 25,
                Condition_LastWeek = 26,
                Condition_NextWeek = 27,
                Condition_In = 28,
                Condition_Between = 29,
                Condition_Null = 30,
                Condition_NotNull = 31
            }

            public MappingType mappingType = MappingType.None;
            public object filterValue;

            public string entityPath
            {
                get
                {
                    if (attributePath.LastIndexOf("/@") >= 0)
                        return attributePath.Substring(0, attributePath.LastIndexOf("/@"));

                    if (attributePath.LastIndexOf("/") >= 0)
                        return attributePath.Substring(0, attributePath.LastIndexOf("/"));

                    return attributePath;
                }
            }

            public string attributeName
            {
                get
                {
                    if (attributePath.LastIndexOf("/@") >= 0)
                        return attributePath.Substring(attributePath.LastIndexOf("/@")+2);

                    if (attributePath.LastIndexOf("/") >= 0)
                        return attributePath.Substring(attributePath.LastIndexOf("/")+1);

                    return attributePath;
                }
            }

            public string entityName
            {
                get
                {
                    if (attributePath.IndexOf("/") >= 0)
                        return attributePath.Substring(0, attributePath.IndexOf("/"));

                    return attributePath;
                }
            }

            public string ImmediateChildOf(string entityPathIn)
            {
                string ep = this.entityPath;

                if (ep.StartsWith(entityPathIn + "/"))
                {
                    int n = ep.IndexOf("/", entityPathIn.Length + 1);

                    if (n >= 0)
                        return ep.Substring(0, n);
                    else
                        return ep;
                }

                return null;
            }

            public string relationshipId
            {
                get
                {
                    int n = this.entityPath.LastIndexOf("/");

                    if (n >= 0)
                        return this.entityPath.Substring(n + 1);

                    return "";
                }
            }

            #region IComparable<BindingDefn> Members

            public int CompareTo(BindingDefn other)
            {
                int comp1 = entityPath.CompareTo(other.entityPath);

                if (comp1 == 0)
                    return attributePath.CompareTo(other.attributePath);
                else
                    return comp1;
            }

            #endregion
        }

        /// <summary>
        /// Display helper
        /// </summary>
        /// <param name="val"></param>
        /// <param name="hasParam"></param>
        /// <returns></returns>
        public static string GetMappingTypeFriendlyName(CRMAPI.BindingDefn.MappingType val, out bool hasParam)
        {
            hasParam = true;

            switch (val)
            {
                case BindingDefn.MappingType.Condition_GT:
                    return "Greater than";

                case BindingDefn.MappingType.Condition_LT:
                    return "Less than";

                case BindingDefn.MappingType.Condition_GTE:
                    return "Greater than or equals";

                case BindingDefn.MappingType.Condition_LTE:
                    return "Less than or equals";

                case BindingDefn.MappingType.Condition_LastMonth:
                    hasParam = false;
                    return "In the last month";

                case BindingDefn.MappingType.Condition_LastWeek:
                    hasParam = false;
                    return "In the last week";

                case BindingDefn.MappingType.Condition_LastYear:
                    hasParam = false;
                    return "In the last year";

                case BindingDefn.MappingType.Condition_NotEquals:
                    return "Not equals";

                case BindingDefn.MappingType.PassiveValue:
                    return "Set Value (if not set)";

                case BindingDefn.MappingType.Value:
                    return "Set Value";

                case BindingDefn.MappingType.Key:
                    return "Equals (key)";

                case BindingDefn.MappingType.Condition_NextMonth:
                    hasParam = false;
                    return "In the next month";

                case BindingDefn.MappingType.Condition_NextWeek:
                    hasParam = false;
                    return "In the next week";

                case BindingDefn.MappingType.Condition_NextYear:
                    hasParam = false;
                    return "In the next year";

                case BindingDefn.MappingType.Condition_ThisMonth:
                    hasParam = false;
                    return "In this month";

                case BindingDefn.MappingType.Condition_ThisYear:
                    hasParam = false;
                    return "This year";

                case BindingDefn.MappingType.Condition_Like:
                    return "Like";

                case BindingDefn.MappingType.Condition_NotLike:
                    return "Not Like";

                case BindingDefn.MappingType.Condition_ThisWeek:
                    hasParam = false;
                    return "This week";

                case BindingDefn.MappingType.Condition_Equals:
                    return "Equals";

                case BindingDefn.MappingType.Condition_Between:
                    return "Between";

                case BindingDefn.MappingType.Condition_In:
                    return "In";

                case BindingDefn.MappingType.Condition_NotNull:
                    hasParam = false;
                    return "Not Null";

                case BindingDefn.MappingType.Condition_Null:
                    hasParam = false;
                    return "Null";

                default:
                    return val.ToString();
            }
        }

        /// <summary>
        /// Binding Attribute class
        /// </summary>
        public class BindingAttribute : Attribute
        {
            public BindingAttribute(BindingDefn bd)
            {
                this.name = bd.attributeName;
                this.ssisType = bd.attributeDefn.ssisType;
                this.crmType = bd.attributeDefn.crmType;
                this.mappingType = bd.mappingType;
                this.filterValue = bd.filterValue;
                this.targetName = bd.targetName;
            }

            public BindingDefn.MappingType mappingType;
            public string targetName;
            public object filterValue;
        }

        /// <summary>
        /// Interface for attribute containment
        /// </summary>
        public interface IAttributeContainer
        {
            string entityName { get; }
            BindingAttribute[] attributes { get; }
            EntityRelationship[] relationships { get; }
        }

        /// <summary>
        /// Entity abstraction
        /// </summary>
        public class EntityBinding : IAttributeContainer
        {
            public string entityName { get; set; }
            public BindingAttribute[] attributes { get; set; }
            public EntityRelationship[] relationships { get; set; }
        }

        /// <summary>
        /// Related entity abstraction
        /// </summary>
        public class EntityRelationship : Relationship, IAttributeContainer
        {
            public string entityName { get; set; }
            public BindingAttribute[] attributes { get; set; }
            public EntityRelationship[] relationships { get; set; }
        }
        #endregion

        #region Helpers to cache
        /// <summary>
        /// CRM type lookup
        /// </summary>
        /// <param name="crmEntityType"></param>
        /// <param name="attrName"></param>
        /// <returns></returns>
        object GetAttributeCRMType(string crmEntityType, string attrName)
        {
            CRMMetaService.RetrieveEntityRequest req = new SSISLib.CRMMetaService.RetrieveEntityRequest();
            req.LogicalName = this.GetEntityName(crmEntityType).ToLower();
            req.EntityItems = SSISLib.CRMMetaService.EntityItems.IncludeAttributes;

            foreach (CRMMetaService.AttributeMetadata adata in (GetMetaService().Execute(req) as CRMMetaService.RetrieveEntityResponse).EntityMetadata.Attributes)
            {
                if (string.Compare(adata.SchemaName.ToString(), attrName, true) == 0)
                {
                    return adata.AttributeType.Value;
                }
            }

            return "Unknown";
        }

        /// <summary>
        /// ID accessor
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        string GetEntityId(SSISLib.CRMService.DynamicEntity entity)
        {
            foreach (CRMService.Property prop in entity.Properties)
            {
                if (prop is CRMService.KeyProperty && prop.Name.ToLower() == entity.Name.ToLower() + "id")
                {
                    return (prop as CRMService.KeyProperty).Value.Value.ToString();
                }
            }

            return null;
        }

        /// <summary>
        /// ID field accessor
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        string GetEntityIdFieldName(SSISLib.CRMService.DynamicEntity entity)
        {
            foreach (CRMService.Property prop in entity.Properties)
            {
                if (prop is CRMService.KeyProperty && prop.Name.ToLower() == entity.Name.ToLower() + "id")
                {
                    return (prop as CRMService.KeyProperty).Name;
                }
            }

            return null;
        }

        /// <summary>
        /// Entity name accessor
        /// </summary>
        /// <param name="entityId"></param>
        /// <returns></returns>
        public string GetEntityName(string entityId)
        {
            if (!entityId.Contains("-"))    // maybe already a name
                return entityId;

            lock (typeof(CRMAPI))
            {
                if (sm_cacheURL == m_url)
                {
                    string resultx = sm_GUIDToNameCache[entityId] as string;

                    if (resultx != null)
                        return resultx;
                }
            }
            
            SSISLib.CRMMetaService.MetadataService service = GetMetaService();

            SSISLib.CRMMetaService.RetrieveEntityRequest req = new SSISLib.CRMMetaService.RetrieveEntityRequest();
            req.MetadataId = new System.Guid(entityId);
            req.EntityItems = SSISLib.CRMMetaService.EntityItems.EntityOnly;

            SSISLib.CRMMetaService.RetrieveEntityResponse resp = service.Execute(req) as SSISLib.CRMMetaService.RetrieveEntityResponse;
            string result = resp.EntityMetadata.SchemaName.ToString();

            lock (typeof(CRMAPI))
            {
                if (sm_cacheURL != m_url)
                {
                    sm_GUIDToNameCache.Clear();
                    sm_NameToGUIDCache.Clear();
                    sm_cacheURL = m_url;

                    sm_GUIDToNameCache[result] = result;
                    sm_GUIDToNameCache[entityId] = result;
                }
            }

            return result;
        }

        /// <summary>
        /// Entity name accessor
        /// </summary>
        /// <param name="entityId"></param>
        /// <returns></returns>
        public string GetEntityID(string entityId)
        {
            if (entityId.Contains("-"))    // maybe already an id
                return entityId;

            lock (typeof(CRMAPI))
            {
                if (sm_cacheURL == m_url)
                {
                    string resultx = sm_NameToGUIDCache[entityId] as string;

                    if (resultx != null)
                        return resultx;
                }
            }

            SSISLib.CRMMetaService.MetadataService service = GetMetaService();

            SSISLib.CRMMetaService.RetrieveEntityRequest req = new SSISLib.CRMMetaService.RetrieveEntityRequest();
            req.LogicalName = entityId;
            req.EntityItems = SSISLib.CRMMetaService.EntityItems.EntityOnly;

            SSISLib.CRMMetaService.RetrieveEntityResponse resp = service.Execute(req) as SSISLib.CRMMetaService.RetrieveEntityResponse;
            string result = resp.EntityMetadata.MetadataId.Value.ToString();

            lock (typeof(CRMAPI))
            {
                if (sm_cacheURL != m_url)
                {
                    sm_NameToGUIDCache.Clear();
                    sm_GUIDToNameCache.Clear();
                    sm_cacheURL = m_url;

                    sm_NameToGUIDCache[result] = result;
                    sm_NameToGUIDCache[entityId] = result;
                }
            }

            return result;
        }
        #endregion

        #region Schema support
        /// <summary>
        /// Data accessor
        /// </summary>
        /// <param name="bindings"></param>
        /// <returns></returns>
        public void GenerateSchema(BindingDefn[] bindings, Relationship[] relationships, System.Xml.Schema.XmlSchemaElement element)
        {
            List<System.Collections.Generic.KeyValuePair<string, object>[]> result = new List<KeyValuePair<string, object>[]>();
            System.Array.Sort(bindings);
            EntityBinding binding = ConvertToEntityBinding(bindings, relationships);
            System.Xml.Schema.XmlSchemaComplexType ct = new System.Xml.Schema.XmlSchemaComplexType();
            ct.Particle = new System.Xml.Schema.XmlSchemaSequence();
            (ct.Particle as System.Xml.Schema.XmlSchemaSequence).Items.Add(new System.Xml.Schema.XmlSchemaElement());
            element.SchemaType = ct;
            GenerateSchema(binding, (ct.Particle as System.Xml.Schema.XmlSchemaSequence).Items[0] as System.Xml.Schema.XmlSchemaElement);
        }

        /// <summary>
        /// Generate schema helper
        /// </summary>
        /// <param name="binding"></param>
        /// <param name="element"></param>
        private void GenerateSchema(IAttributeContainer binding, System.Xml.Schema.XmlSchemaElement element)
        {
            element.Name = GetEntityName(binding.entityName);
            element.MinOccurs = 0;
            element.MaxOccurs = ((binding as EntityRelationship) == null ? int.MaxValue : ((binding as EntityRelationship).type == Relationship.RelationshipType.ManyToOne) ? 1 : int.MaxValue);

            if (element.SchemaType == null)
                element.SchemaType = new System.Xml.Schema.XmlSchemaComplexType();

            System.Xml.Schema.XmlSchemaComplexType ct = element.SchemaType as System.Xml.Schema.XmlSchemaComplexType;

            System.Xml.Schema.XmlSchemaAttribute idattr = new System.Xml.Schema.XmlSchemaAttribute();
            idattr.Name = "ID";
            ct.Attributes.Add(idattr);

            foreach (var a in binding.attributes)
            {
                // add attributes
                System.Xml.Schema.XmlSchemaAttribute attr = new System.Xml.Schema.XmlSchemaAttribute();
                attr.Name = a.name;
                ct.Attributes.Add(attr);
            }

            // add Action node
            if (ct.Particle == null)
                ct.Particle = new System.Xml.Schema.XmlSchemaSequence();

            System.Xml.Schema.XmlSchemaElement actionEl = new System.Xml.Schema.XmlSchemaElement();
            actionEl.Name = "Action";

            actionEl.MinOccurs = 0;
            actionEl.MaxOccurs = 1;
            (ct.Particle as System.Xml.Schema.XmlSchemaSequence).Items.Add(actionEl);

            System.Xml.Schema.XmlSchemaAttribute actionAttr = new System.Xml.Schema.XmlSchemaAttribute();
            actionAttr.Name = "ActionType";
            System.Xml.Schema.XmlSchemaSimpleType st = new System.Xml.Schema.XmlSchemaSimpleType();
            actionAttr.SchemaType = st;
            st.Content = new System.Xml.Schema.XmlSchemaSimpleTypeRestriction();
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).BaseTypeName = new System.Xml.XmlQualifiedName("string", "http://www.w3.org/2001/XMLSchema"); 
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).Facets.Add(new System.Xml.Schema.XmlSchemaEnumerationFacet() { Value = "Insert" });
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).Facets.Add(new System.Xml.Schema.XmlSchemaEnumerationFacet() { Value = "Update" });
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).Facets.Add(new System.Xml.Schema.XmlSchemaEnumerationFacet() { Value = "Delete" });
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).Facets.Add(new System.Xml.Schema.XmlSchemaEnumerationFacet() { Value = "Upsert" });
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).Facets.Add(new System.Xml.Schema.XmlSchemaEnumerationFacet() { Value = "Upsert_AllChildren" });
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).Facets.Add(new System.Xml.Schema.XmlSchemaEnumerationFacet() { Value = "Update_AllChildren" });
            (st.Content as System.Xml.Schema.XmlSchemaSimpleTypeRestriction).Facets.Add(new System.Xml.Schema.XmlSchemaEnumerationFacet() { Value = "Unchanged" }); 

            System.Xml.Schema.XmlSchemaComplexType actct = new System.Xml.Schema.XmlSchemaComplexType();
            actct.Particle = new System.Xml.Schema.XmlSchemaSequence();

            actionEl.SchemaType = actct;
            actct.Attributes.Add(actionAttr);

            // add Key element
            System.Xml.Schema.XmlSchemaElement keyEl = new System.Xml.Schema.XmlSchemaElement();
            keyEl.Name = "Key";
            keyEl.MinOccurs = 0;
            keyEl.MaxOccurs = 1;
            (actct.Particle as System.Xml.Schema.XmlSchemaSequence).Items.Add(keyEl);

            System.Xml.Schema.XmlSchemaComplexType keyct = new System.Xml.Schema.XmlSchemaComplexType();
            keyEl.SchemaType = keyct;

            // add attributes
            idattr = new System.Xml.Schema.XmlSchemaAttribute();
            idattr.Name = "ID";
            idattr.Use = System.Xml.Schema.XmlSchemaUse.Optional;
            keyct.Attributes.Add(idattr);

            foreach (var a in binding.attributes)
            {
                System.Xml.Schema.XmlSchemaAttribute attr = new System.Xml.Schema.XmlSchemaAttribute();
                attr.Name = a.name;
                attr.Use = System.Xml.Schema.XmlSchemaUse.Optional;
                keyct.Attributes.Add(attr);
            }           

            // add Relationships...
            foreach (var r in binding.relationships)
            {
                System.Xml.Schema.XmlSchemaElement relElement = new System.Xml.Schema.XmlSchemaElement();
                (ct.Particle as System.Xml.Schema.XmlSchemaSequence).Items.Add(relElement);
                GenerateSchema(r, relElement);
            }
        }
        #endregion

        /// <summary>
        /// Conversion helper
        /// </summary>
        /// <param name="v"></param>
        /// <returns></returns>
        private static int SafeGetInt(object v)
        {
            return SafeGetInt(v, 0);
        }

        /// <summary>
        /// Conversion helper
        /// </summary>
        /// <param name="v"></param>
        /// <returns></returns>
        private static int SafeGetInt(object v, int def)
        {
            if (v == null)
                return def;

            try
            {
                return System.Convert.ToInt32(v);
            }
            catch
            {
            }

            return def;
        }
    }
}
